%load_ext autoreload
%autoreload 2

import os
import sys
import warnings
from calendar import day_name
from glob import glob
from itertools import product
from typing import Dict

import altair as alt
import duckdb
import pandas as pd
from scipy.stats import linregress
from watermark import watermark

_ = alt.data_transformers.disable_max_rows()
_ = alt.renderers.set_embed_options(actions=False)

PROJ_ROOT = os.pardir
src_dir = os.path.join(PROJ_ROOT, "src")
sys.path.append(src_dir)

%aimport file_utils
import file_utils as flut

%aimport open_data
import open_data as od

%aimport pandas_utils
import pandas_utils as pu

%aimport visualization_helpers
import visualization_helpers as vzu

# ridership
years_proc_trips = {
    2018: [f'Q{k}' for k in range(1, 4+1)],
    2019: [f'Q{k}' for k in range(1, 4+1)],
    2020: [f'{str(k).zfill(2)}' for k in range(1, 12+1)],
    2021: [f'{str(k).zfill(2)}' for k in range(1, 12+1)],
    2022: [f'{str(k).zfill(2)}' for k in range(1, 12+1)],
    2023: [f'{str(k).zfill(2)}' for k in range(1, 3+1)],
}

# exploring turning (inflection) point
years_temp_dependence = [2018, 2019, 2020, 2021, 2022]

# plotting
axis_label_fontsize = 14

# exporting to disk
my_timezone = 'America/Toronto'

data_dir = os.path.join(PROJ_ROOT, 'data')
raw_data_dir = os.path.join(data_dir, 'raw', 'systems', 'toronto')
processed_data_dir = os.path.join(data_dir, 'processed')
reports_dir = os.path.join(PROJ_ROOT, 'reports')
figures_dir = os.path.join(reports_dir, 'figures')

# processed trips
fpaths_proc = {
    y: [
        f
        for p in periods
        for f in sorted(
            glob(
                os.path.join(
                    processed_data_dir,
                    f'processed__trips_{y}_{p}*.parquet.gzip',
                )
            )
        )
    ]
    for y, periods in years_proc_trips.items()
}
fpaths_proc_all = [f for _, v in fpaths_proc.items() for f in v]
fpaths_proc_2018_2022 = [f for y in range(2018, 2022+1) for f in fpaths_proc[y]]

# downtown neighbourhoods
fpath_downtown_neighs = glob(
    os.path.join(raw_data_dir, 'downtown_neighbourhoods__*.parquet.gzip')
)[0]

# expansion plans
fpath_expansion = glob(
    os.path.join(raw_data_dir, 'network_expansion__*.parquet.gzip')
)[0]

# station info for currently active stations
fpath_stations_info = glob(
    os.path.join(raw_data_dir, 'stations_info__*.parquet.gzip')
)[0]

# daily weather data
fpath_weather = glob(
    os.path.join(raw_data_dir, 'daily_weather__*.parquet.gzip')
)[0]

# holidays
fpath_holidays = glob(
    os.path.join(raw_data_dir, 'holidays__*.parquet.gzip')
)[0]

# top performing stations
fpath_top_stations = glob(
    os.path.join(processed_data_dir, 'stations_performance__*.parquet.gzip')
)[0]

def run_sql_query(query: str, verbose: bool=False) -> pd.DataFrame:
    """Run SQL query using DuckDB."""
    with warnings.catch_warnings():
        warnings.simplefilter("ignore", FutureWarning)
        df_query = duckdb.sql(query).df()
    if verbose:
        print(f"Query returned {len(df_query):,} rows")
    return df_query

%%time
query = f"""
        SELECT *
        FROM read_parquet({[fpath_weather]})
        """
df_weather = run_sql_query(query).convert_dtypes()
with pd.option_context('display.max_columns', None):
    pu.show_df(df_weather)

CPU times: user 23.1 ms, sys: 0 ns, total: 23.1 ms
Wall time: 22.7 ms

df_downtown_neighs = pd.read_parquet(fpath_downtown_neighs)
pu.show_df(df_downtown_neighs)

%%time
query = f"""
        SELECT station_id,
               name,
               physical_configuration,
               capacity,
               is_charging_station,
               rental_methods LIKE '%CREDITCARD%' AS credit,
               Neighbourhood,
               COALESCE(Location, NULL, 'Others') AS Location,
               COALESCE(is_downtown, NULL, False) AS is_downtown,
               census_tract_id
        FROM read_parquet({[fpath_stations_info]})
        LEFT JOIN df_downtown_neighs USING (Neighbourhood)
        -- WHERE physical_configuration <> 'VAULT'
        ORDER BY station_id, name
        """
df_info = run_sql_query(query).convert_dtypes()
with pd.option_context('display.max_columns', None):
    pu.show_df(df_info)

CPU times: user 23.3 ms, sys: 0 ns, total: 23.3 ms
Wall time: 20.4 ms

%%time
query = f"""
        SELECT *,
               NULL AS frac_ctracts_with_bikeshare
        FROM read_parquet({[fpath_expansion]})
        """
df_network_size = run_sql_query(query).convert_dtypes()
with pd.option_context('display.max_columns', None):
    pu.show_df(df_network_size)

CPU times: user 9.5 ms, sys: 1.12 ms, total: 10.6 ms
Wall time: 9.94 ms

%%time
query = f"""
        SELECT *,
               (
                   CASE WHEN DAYNAME(date) IN ('Sunday', 'Saturday')
                   THEN 'Monday'
                   ELSE DAYNAME(date)
                   END
               ) AS weekday
        FROM read_parquet({[fpath_holidays]})
        """
df_holidays = run_sql_query(query).convert_dtypes()
with pd.option_context('display.max_columns', None):
    pu.show_df(df_holidays)

CPU times: user 6.96 ms, sys: 3.23 ms, total: 10.2 ms
Wall time: 9.74 ms

df_stations = pd.read_parquet(fpath_top_stations)
pu.show_df(df_stations)

%%time
query = f"""
        SELECT *
        FROM read_parquet({[fpaths_proc[2022][7]]})
        WHERE started_at_year = 2022
        AND started_at_month = 8
        LIMIT 3
        """
df_proc_trips_preview = run_sql_query(query).convert_dtypes()
pu.show_df(df_proc_trips_preview)

CPU times: user 48.9 ms, sys: 558 µs, total: 49.4 ms
Wall time: 48.7 ms

%%time
query = f"""
        -- 1. get pre-2023 yearly departure-related totals (up to Dec 31, 2022) per station
        WITH t1 AS (
            SELECT start_station_id AS station_id,
                   started_at_year AS year,
                   COUNT(DISTINCT(trip_id)) AS trips,
                   COUNT(DISTINCT(bike_id)) AS bikes_deps
            FROM read_parquet({fpaths_proc_all})
            WHERE started_at_year <= 2022
            -- (OPTIONAL) exclude trips missing a start station name
            -- WHERE start_station_name IS NOT NULL
            GROUP BY all
        ),
        -- 2. get pre-2023 yearly arrival-related totals (up to Dec 31, 2022) per station
        t2 AS (
            SELECT end_station_id AS station_id,
                   ended_at_year AS year,
                   COUNT(DISTINCT(bike_id)) AS bikes_arrs
            FROM read_parquet({fpaths_proc_all})
            WHERE started_at_year <= 2022
            -- (OPTIONAL) exclude trips missing a end station name
            -- WHERE end_station_name IS NOT NULL
            GROUP BY all
        ),
        -- 3. Aggregate pre-2023 yearly departures to get the following
        -- count the stations with departures
        -- count the number of bikes used for arrivals
        -- sum the number of departures (trips)
        t3 AS (
            SELECT year,
                   COUNT(distinct(station_id)) AS num_stations,
                   MAX(bikes_deps) AS bikes,
                   SUM(trips) AS trips,
                   'departures' AS type
            FROM t1
            GROUP BY year
        ),
        -- 4. Aggregate pre-2023 yearly arrivals to get the following
        -- count the stations with arrivals
        -- count the number of bikes used for arrivals
        t4 AS (
            SELECT year,
                   COUNT(distinct(station_id)) AS num_stations,
                   MAX(bikes_arrs) AS bikes,
                   NULL AS trips,
                   'arrivals' AS type
            FROM t2
            GROUP BY year
        ),
        -- 5. combine pre-2023 counts for departures and arrivals
        t5 AS (
            SELECT *
            FROM t3
            UNION ALL
            SELECT *
            FROM t4
        ),
        -- 6. get overal trips, stations, and bikes with bike share trips
        t6 AS (
            SELECT year,
                   SUM(trips) AS trips,
                   MAX(num_stations) AS num_stations,
                   MAX(bikes) AS num_bikes
            FROM t5
            GROUP BY ALL
            ORDER BY year
        ),
        -- 7. combine the yearly totals with the network expansion plans starting in 2023
        t7 AS (
            SELECT *
            FROM t6
            WHERE year < 2023
            UNION
            SELECT year,
                   trips,
                   num_stations,
                   num_bikes
            FROM df_network_size
            WHERE year >= 2023
            ORDER BY year
        ),
        -- 8. add indicator of future expansion plans
        t8 AS (
            SELECT *,
                   2023 AS shading_start,
                   2025 AS shading_stop
            FROM t7
        )
        SELECT *
        FROM t8
        """
df_yearly_summary = run_sql_query(query).convert_dtypes()
pu.show_df(df_yearly_summary)

CPU times: user 21.7 s, sys: 1.35 s, total: 23.1 s
Wall time: 2.36 s

%%time
chart, charts_dict = vzu.plot_line_chart_grid(
    df=df_yearly_summary,
    xvar='year:O',
    yvars=["trips:Q", 'num_stations:Q', 'num_bikes:Q'],
    xvar_areas="shading_start:O",
    xvar_areas2="shading_stop:O",
    color_by_col_areas = 'index:N',
    areas_opacity = 0.1,
    axis_label_color = '#636363',
    axis_tick_label_color = 'grey',
    annotation_text_color = 'grey',
    ptitles={
        'trips': "Rapily growing ridership with nearly 5M trips in 2022",
        'num_stations': 'Strong yearly expansion of network footprint to resume in 2023',
        'num_bikes': 'Yearly expansion of bicycle inventory to restart in 2023',
    },
    ptitles_x_locs={'trips': 45, 'num_stations': 65, 'num_bikes': 70},
    ytitles={
        'trips': 'Trips', 'num_stations': 'Stations', 'num_bikes': 'Bicycles'
    },
    line_color = 'darkgreen',
    marker_size = 60,
    marker_fill = "white",
    marker_edge_color = 'darkgreen',
    annotation_text_y_loc = 140,
    annotation_text_opacity = 0.85,
    concat_spacing=10,
    axis_label_opacity=0.7,
    axis_label_fontsize=14,
    fig_size=dict(width=675, height=300),
)
chart.save(os.path.join(figures_dir, '01_high_level_summary.png'))
for k,v in charts_dict.items():
    chart = vzu.configure_chart(charts_dict[k], axis_label_fontsize)
    chart.save(os.path.join(figures_dir, f'01_high_level_summary__{k}.png'))

CPU times: user 890 ms, sys: 25.5 ms, total: 916 ms
Wall time: 812 ms

%%time
chart = vzu.plot_line_charts_with_shaded_area(
    pd.concat(
        [
            df_yearly_summary.query("year <= 2022"),
            df_yearly_summary.query("year >= 2023").drop(columns=['trips'])
        ]
    ),
    xvar="year:O",
    yvar="trips:Q",
    yvar2="num_stations:Q",
    xvar_areas="shading_start:O",
    xvar_areas2="shading_stop:O",
    color_by_col_areas = 'index:N',
    y_max = 9_000_000,
    xtitle = 'Trips (solid)',
    ytitle = 'Stations (dashed)',
    axis_label_fontsize = axis_label_fontsize,
    ptitle = alt.TitleParams(
        "Nearly 5M trips in 2022 and Expanding the Network's Footprint",
        anchor='start',
        dx=45,
        fontSize=axis_label_fontsize
    ),
    annotation_text = 'Four-Year Growth Plan',
    annotation_text_color = 'grey',
    annotation_text_opacity = 0.85,
    annotation_text_x_loc = 160,
    annotation_text_y_loc = 125,
    annotation_text_solid=dict(dx=175, dy=75),
    annotation_text_dashed=dict(dx=330, dy=-40),
    areas_opacity = 0.1,
    axis_label_color = 'darkgreen',
    axis_tick_label_color = 'darkgreen',
    axis2_label_color = 'green',
    axis2_tick_label_color = 'green',
    line_color = 'darkgreen',
    line_color2 = '#a1d99b',
    marker_size = 60,
    marker_fill = "white",
    marker_edge_color = 'darkgreen',
    marker_edge_color2 = '#a1d99b',
    fig_size = dict(width=675, height=300),
)
chart

CPU times: user 43.5 ms, sys: 3 µs, total: 43.5 ms
Wall time: 43.1 ms

%%time
chart = vzu.plot_line_charts_with_shaded_area(
    df_yearly_summary.query('year != 2018'),
    xvar="year:O",
    yvar="num_stations:Q",
    yvar2="num_bikes:Q",
    xvar_areas="shading_start:O",
    xvar_areas2="shading_stop:O",
    color_by_col_areas = 'index:N',
    y_max = 1_200,
    xtitle = 'Stations (solid)',
    ytitle = 'Bicycles (dashed)',
    axis_label_fontsize = axis_label_fontsize,
    ptitle = alt.TitleParams(
        "Located in Half the City's Neighbourhoods and Expanding Bicycle Inventory",
        anchor='start',
        dx=65,
        fontSize=axis_label_fontsize
    ),
    annotation_text = 'Four-Year Growth Plan',
    annotation_text_color = 'grey',
    annotation_text_opacity = 0.85,
    annotation_text_x_loc = 170,
    annotation_text_y_loc = 125,
    annotation_text_solid=dict(dx=200, dy=5),
    annotation_text_dashed=dict(dx=280, dy=-50),
    areas_opacity = 0.1,
    axis_label_color = 'darkgreen',
    axis_tick_label_color = 'darkgreen',
    axis2_label_color = 'green',
    axis2_tick_label_color = 'green',
    line_color = 'darkgreen',
    line_color2 = '#a1d99b',
    marker_size = 60,
    marker_fill = "white",
    marker_edge_color = 'darkgreen',
    marker_edge_color2 = '#a1d99b',
    fig_size = dict(width=675, height=300),
)
chart

CPU times: user 39.1 ms, sys: 4.32 ms, total: 43.5 ms
Wall time: 42.8 ms

%%time
query = f"""
        WITH t1 AS (
            -- 1. count number of stations by station and location type
            SELECT -- change the top-performing station column from a boolean to a string
                   (
                       CASE WHEN is_top_perform_station = True
                       THEN 'Top Performer'
                       ELSE 'Others' END
                   ) AS station_type,
                   -- append conditional to indicate if station is located in a downtown neighbourhood
                   (
                       CASE WHEN is_downtown = True
                       THEN 'Downtown'
                       ELSE 'Other_Locations' END
                   ) AS location_type,
                   -- count the number of stations that are and are not located in a downtown
                   -- neighbourhood
                   COUNT(DISTINCT(station_id)) AS num_stations
            FROM df_stations
            GROUP BY ALL
        ),
        -- 2. reshape using pivot table
        t2 AS (
            PIVOT t1
            ON location_type
            USING sum(num_stations)
        )
        -- 3. calculate fraction of stations that are and are not located in downtown neighbourhoods
        SELECT *,
               Downtown+Other_Locations AS total,
               100*Downtown/total AS frac_downtown
        FROM t2
        """
df_downtown_query = run_sql_query(query).convert_dtypes()
df_downtown_query

CPU times: user 29 ms, sys: 0 ns, total: 29 ms
Wall time: 17.3 ms

%%time
chart = vzu.plot_bar_chart(
    df_downtown_query,
    xvar='frac_downtown:Q',
    yvar='station_type:N',
    xtitle='Downtown Stations (%)',
    y_axis_sort=['Top Performer', 'Others'],
    color_by_col='station_type:N',
    color_labels=['Top Performer', 'Others'],
    color_values=['darkgreen', 'lightgrey'],
    ptitle=alt.TitleParams(
        'Nearly 3X Top-Performers are Downtown as Others',
        anchor='start',
        dx=100,
        fontSize=axis_label_fontsize
    ),
    axis_label_fontsize=axis_label_fontsize,
    fig_size=dict(width=450, height=125),
)
chart.save(
    os.path.join(figures_dir, '02_top_performers_downtown.png')
)
chart

CPU times: user 65.2 ms, sys: 6.22 ms, total: 71.4 ms
Wall time: 41.2 ms

%%time
query = f"""
        WITH t1 AS (
            -- 1. count number of stations by station and neighbourhood
            SELECT -- change the top-performing station column from a boolean to a string
                   (
                       CASE WHEN is_top_perform_station = True
                       THEN 'Top Performer'
                       ELSE 'Others' END
                   ) AS station_type,
                   -- append conditional to indicate if station is located in a downtown neighbourhood
                   REPLACE(location, ' ', '_') AS location,
                   -- count the number of stations that are and are not located in a downtown
                   -- neighbourhood
                   COUNT(DISTINCT(station_id)) AS num_stations
            FROM df_stations
            GROUP BY ALL
        ),
        -- 2. reshape using pivot table
        t2 AS (
            PIVOT t1
            ON location
            USING sum(num_stations)
        ),
        -- 3. calculate fraction of stations by neighbourhood
        t3 AS (
            SELECT *,
                   Downtown+East_of_Downtown+Others+West_of_Downtown AS total,
                   100*Downtown/total AS frac_Downtown,
                   100*East_of_Downtown/total AS frac_East_of_Downtown,
                   100*West_of_Downtown/total AS frac_West_of_Downtown,
                   100*Others/total AS frac_Other_Neighbourhoods
            FROM t2
        ),
        -- 4. reshape into tidy data
        t4 AS (
            UNPIVOT (
                SELECT station_type,
                       frac_downtown,
                       frac_East_of_Downtown,
                       frac_West_of_Downtown,
                       frac_Other_Neighbourhoods
                FROM t3
            )
            ON frac_downtown,
               frac_East_of_Downtown,
               frac_West_of_Downtown,
               frac_Other_Neighbourhoods
            INTO NAME variable VALUE value
        )
        SELECT * EXCLUDE(variable),
               REPLACE(
                   REPLACE(variable, 'frac_', ''),
                   '_',
                   ' '
               ) AS variable
        FROM t4
        """
df_downtown_adjacent_query = run_sql_query(query).convert_dtypes()
df_downtown_adjacent_query

CPU times: user 46.6 ms, sys: 0 ns, total: 46.6 ms
Wall time: 31.5 ms

%%time
chart = vzu.plot_non_grouped_bar_chart_grid(
    df=df_downtown_adjacent_query,
    rows_labels_colors={
        'Top Performer': {
            'labels': [
                'Downtown',
                'West of Downtown',
                'Other Neighbourhoods',
                'East of Downtown',
            ],
            'colors': [
                'darkgreen',
                'darkred',
                'lightgrey',
                '#a1d99b',
            ],
        },
        'Others': {
            'labels': [
                'Other Neighbourhoods',
                'Downtown',
                'West of Downtown',
                'East of Downtown',
            ],
            'colors': [
                'lightgrey',
                'darkgreen',
                'darkred',
                '#a1d99b',
            ],
        }
    },
    xvar='value:Q',
    yvar='variable:N',
    color_by_col='variable:N',
    legend_label_font_color='#636363',
    title_text='Downtown and West Outperform Other Neighbourhoods',
    xtitle_text='Stations (%)',
    title_fontweight='normal',
    title_font_color='grey',
    axis_label_fontsize=14,
    ptitle_xloc=20,
    fig_size = dict(width=450, height=125),
)
chart.save(
    os.path.join(
        figures_dir, '03_top_performers_downtown_neighbourhoods.png'
    )
)
chart

CPU times: user 130 ms, sys: 0 ns, total: 130 ms
Wall time: 83.4 ms

%%time
query = f"""
        WITH t1 AS (
            -- 1. count number of stations by station type and whether they accept credit card
            -- payments
            SELECT -- change the top-performing station column from a boolean to a string
                   (
                       CASE WHEN is_top_perform_station = True
                       THEN 'Top Performer'
                       ELSE 'Others' END
                   ) AS station_type,
                   -- indicate if station accepts payments using a credit card
                   (
                       CASE WHEN credit = True
                       THEN 'Credit_accepted'
                       ELSE 'Credit_not_accepted' END
                   ) AS takes_credit,
                   -- count the number of stations that do and do not accept payments using a
                   -- credit card
                   COUNT(DISTINCT(station_id)) AS num_stations
            FROM df_stations
            GROUP BY ALL
        ),
        -- 2. reshape using pivot table
        t2 AS (
            PIVOT t1
            ON takes_credit
            USING sum(num_stations)
        )
        -- 3. calculate fraction of stations that do and do not accept credit card payments
        SELECT *,
               Credit_accepted+Credit_not_accepted AS total,
               100*Credit_accepted/total AS frac_takes_credit
        FROM t2
        """
df_payment_methods_query = run_sql_query(query).convert_dtypes()
df_payment_methods_query

CPU times: user 31.9 ms, sys: 0 ns, total: 31.9 ms
Wall time: 20 ms

%%time
chart = vzu.plot_bar_chart(
    df_payment_methods_query,
    xvar='frac_takes_credit:Q',
    yvar='station_type:N',
    xtitle='Stations (%)',
    y_axis_sort=['Top Performer', 'Others'],
    color_by_col='station_type:N',
    color_labels=['Top Performer', 'Others'],
    color_values=['darkgreen', 'lightgrey'],
    ptitle=alt.TitleParams(
        'Stations Accept Credit Card as a Method of Payment',
        anchor='start',
        dx=100,
        fontSize=axis_label_fontsize
    ),
    axis_label_fontsize=axis_label_fontsize,
    fig_size=dict(width=450, height=125),
)
chart.save(
    os.path.join(
        figures_dir, '04_top_performers_payment_methods.png'
    )
)
chart

CPU times: user 36.4 ms, sys: 0 ns, total: 36.4 ms
Wall time: 31 ms

%%time
query = f"""
        WITH t1 AS (
            -- 1. count number of stations by station type and configuration
            SELECT -- change the top-performing station column from a boolean to a string
                   (
                       CASE WHEN is_top_perform_station = True
                       THEN 'Top Performer'
                       ELSE 'Others' END
                   ) AS station_type,
                   -- extract station configuration (REGULAR, *MAPFRAME, ELECTRICBIKESTATION)
                   physical_configuration,
                   -- count stations
                   COUNT(DISTINCT(station_id)) AS num_stations
            FROM df_stations
            GROUP BY ALL
        ),
        -- 2. reshape into untidy data using pivot table
        t2 AS (
            PIVOT t1
            ON physical_configuration
            USING sum(num_stations)
        ),
        -- 3. create a new physical configuration as no-kiosk stations which includes
        -- all MAPFRAME configurations combined
        t3 AS (
            SELECT *,
                   -- calculate total number of stations in each category
                   (
                       COALESCE(ELECTRICBIKESTATION, NULL, 0)
                       +COALESCE(REGULAR, NULL, 0)
                       +COALESCE(REGULARLITMAPFRAME, NULL, 0)
                       +COALESCE(SMARTMAPFRAME, NULL, 0)
                       +COALESCE(SMARTLITMAPFRAME, NULL, 0)
                   ) AS total,
                   -- calculate total number of stations in the new no-kiosk (*MAPFRAME) category
                   (
                       COALESCE(REGULARLITMAPFRAME, NULL, 0)
                       +COALESCE(SMARTMAPFRAME, NULL, 0)
                       +COALESCE(SMARTLITMAPFRAME, NULL, 0)
                   ) AS No_Kiosk
            FROM t2
        ),
        -- 4. calculate fraction of stations in REGULAR, NO-KIOSK, VAULT & ELECTRICBIKESTATION configs
        t4 AS (
            SELECT station_type,
                   100*COALESCE(ELECTRICBIKESTATION, NULL, 0)/total AS Electric,
                   100*COALESCE(REGULAR, NULL, 0)/total AS Regular,
                   100*COALESCE(VAULT, NULL, 0)/total AS Vault,
                   100*COALESCE(SMARTMAPFRAME, NULL, 0)/total AS 'No Kiosk'
            FROM t3
        ),
        -- 5. reshape into tidy data using pivot table
        t5 AS (
            UNPIVOT t4
            ON Electric, Regular, Vault, 'No Kiosk'
            INTO NAME physical_configuration
            VALUE value
        )
        SELECT *
        FROM t5
        """
df_physical_config_query = run_sql_query(query).convert_dtypes()
pu.show_df(df_physical_config_query)

CPU times: user 108 ms, sys: 21.2 ms, total: 129 ms
Wall time: 96.8 ms

%%time
chart = vzu.plot_grouped_bar_chart(
    df=df_physical_config_query,
    xvar="physical_configuration:N",
    yvar="sum(value):Q",
    color_by_col="physical_configuration:N",
    column_var="station_type:N",
    ytitle='Stations (%)',
    color_labels=['Regular', 'No Kiosk', 'Vault', 'Electric'],
    color_values=['darkgreen', 'lightgrey', 'lightblue', 'lightgreen'],
    axis_label_fontsize=14,
    ptitle=alt.TitleParams(
        'Top-Performing Stations Have Preference for Guidance Kiosk',
        anchor='start',
        dx=20,
        fontSize=axis_label_fontsize,
    ),
    facet_column_spacing=20,
    fig_size=dict(width=250, height=300),
)
chart.save(
    os.path.join(
        figures_dir, '05_top_performers_physical_configurations.png'
    )
)
chart

CPU times: user 58.2 ms, sys: 302 µs, total: 58.5 ms
Wall time: 50.4 ms

def get_station_type(
    df: pd.DataFrame, station_type_mapper: Dict[str, str]
) -> pd.DataFrame:
    """Get fraction of simultaneous overall, weekday & weekend top-performers."""
    df_station_types = pd.DataFrame.from_records(
        [
            {
                'station_type': label,
                "num_stations": len(df.query(query)),
                'frac_stations_overall': (
                    100*len(df.query(query))/len(df.query("is_top_perform_station"))
                ),
            }
            for query, label in station_type_mapper.items()
        ]
    )
    return df_station_types

station_type_criteria = {
    (
        "(is_top_perform_station_weekday == True) & "
        "(is_top_perform_station_weekend == True) & "
        "(is_top_perform_station == True)"
    ): 'Includes Weekdays & Weekends',
    (
        '((is_top_perform_station_weekday == False) | '
        '(is_top_perform_station_weekend == False)) & '
        '(is_top_perform_station == True)'
    ): 'Excludes Weekdays or Weekends',
}
df_top_perform_overall_intra_week = (
    get_station_type(df_stations, station_type_criteria)
    .convert_dtypes()
)
pu.show_df(df_top_perform_overall_intra_week)

%%time
chart = vzu.plot_pie_chart(
    df_top_perform_overall_intra_week,
    yvar = "frac_stations_overall:Q",
    color_by_col = "station_type:N",
    ptitle=alt.TitleParams(
        'Most Top-Performers Show Consistent Intra-Week Performance',
        anchor='start',
        dx=0,
        fontSize=axis_label_fontsize
    ),
    label_non_white_color = 'Includes Weekdays & Weekends',
    radius = 180,
    text_radius_offset = 230,
    axis_label_fontsize = 14,
    annotation_label_fontsize = 18,
    annotation_radius = 90,
    yvar_non_white_threshold = 25,
    stroke_thickness = 5,
    x_loc_annotation = 0,
    x_loc_label = 40,
    colors = {
        'Includes Weekdays & Weekends': 'darkgreen',
        'Excludes Weekdays or Weekends': '#c7e9c0',
    },
)
chart.save(
    os.path.join(
        figures_dir, '06_top_performers_day_of_week.png'
    )
)
chart

CPU times: user 59.8 ms, sys: 46 µs, total: 59.8 ms
Wall time: 57.5 ms

%%time
query = f"""
        WITH t1 AS (
            -- 1. get number of monthly departures per station, user type and month in 2018
            SELECT start_station_id AS station_id,
                   started_at_year AS year,
                   started_at_month AS month,
                   REPLACE(user_type, ' Member', '') AS user_type,
                   COUNT(DISTINCT(trip_id)) AS trips,
                   'departures' AS type
            FROM read_parquet({fpaths_proc[2018]})
            GROUP BY all
        ),
        t2 AS (
            -- 2. get number of monthly arrivals per station, user type and month in 2018
            SELECT end_station_id AS station_id,
                   ended_at_year AS year,
                   ended_at_month AS month,
                   REPLACE(user_type, ' Member', '') AS user_type,
                   COUNT(DISTINCT(trip_id)) AS trips,
                   'arrivals' AS type
            FROM read_parquet({fpaths_proc[2018]})
            WHERE ended_at_year = 2018
            GROUP BY all
        ),
        -- 3. combine monthly departures and arrivals per station
        -- first use UNION to capture stations with only departures or only arrivals
        -- then reshape into untidy data to get departures & arrivals per station as columns
        t3 AS (
            PIVOT (
                SELECT *
                FROM t1
                UNION
                SELECT *
                FROM t2
            )
            ON type USING MAX(trips)
        ),
        -- 4. LEFT JOIN with table indicating if station is a top-performer
        t4 AS (
            SELECT *
            FROM t3
            LEFT JOIN (
                SELECT station_id,
                       is_top_perform_station
                FROM df_stations
            ) USING (station_id)
        ),
        -- 5. fill any missing values in the is_top_perform_station column after above
        -- LEFT JOIN with FALSE
        t5 AS (
            SELECT --fill missing values in is_top_perform_station with FALSE
                   * EXCLUDE(is_top_perform_station, departures, arrivals),
                   COALESCE(is_top_perform_station, NULL, False) AS is_top_perform_station,
                   COALESCE(departures, NULL, 0) AS departures,
                   COALESCE(arrivals, NULL, 0) AS arrivals
            FROM t4
        ),
        -- 6. calculate average monthly trips per station, station type and user type
        t6 AS (
            SELECT -- change the top-performing station column from a boolean to a string
                   (
                       CASE WHEN is_top_perform_station = True
                       THEN 'top-performer'
                       ELSE 'others' END
                   ) AS station_type,
                   user_type,
                   year,
                   month,
                   -- number of stations used
                   COUNT(DISTINCT(station_id)) AS num_stations_used,
                   -- strftime(make_date(year, month, 1), '%Y-%m') AS date_ym,
                   make_date(year, month, 1) AS date_ym,
                   -- get departures
                   SUM(departures) AS departures,
                   -- get arrivals
                   SUM(arrivals) AS arrivals,
                   -- get average departures
                   SUM(departures)/num_stations_used AS avg_departures_per_station,
                   -- get average arrivals
                   SUM(arrivals)/num_stations_used AS avg_arrivals_per_station,
                   strftime(make_date(2018, 7, 1), '%Y-%m') AS peak_ridership
            FROM t5
            GROUP BY ALL
            ORDER BY ALL
        )
        SELECT *
        FROM t6
        """
df_by_month_2018 = run_sql_query(query).convert_dtypes()
pu.show_df(df_by_month_2018)

CPU times: user 3.4 s, sys: 61.8 ms, total: 3.46 s
Wall time: 536 ms

%%time
for yvar, title_text in zip(
    ['avg_departures_per_station:Q', 'avg_arrivals_per_station:Q'],
    ['Departures', 'Arrivals'],
):
    chart = vzu.plot_grouped_line_charts(
        df_by_month_2018,
        pd.DataFrame({'date_ym': [pd.to_datetime('2018-07-01')]}),
        xvar="yearmonth(date_ym):T",
        yvar=yvar,
        color_by_col="user_type:N",
        color_labels=['Annual', 'Casual'],
        color_values=['darkgreen', '#a1d99b'],
        legend=alt.Legend(
            titleFontSize=axis_label_fontsize,
            labelFontSize=axis_label_fontsize,
            labelColor='black',
            orient='bottom',
            direction='horizontal',
            titleOrient='left',
        ),
        annotation_text='Middle of year',
        annotation_text_color='red',  # 'grey'
        annotation_text_opacity=0.5,
        annotation_text_x_loc=-60,
        annotation_text_y_loc=-135,
        xvar_rule="yearmonth(peak_ridership):T",
        color_rule='',  # '#cccccc'
        title_text=f'Peak 2018 {title_text} Occurs During Warmest Months',
        sub_title_text_color='#7f7f7f',
        sub_title_vertical_padding=5,
        axis_label_fontsize=14,
        x_axis_ticks=None,
        concat='column',
        fig_size=dict(width=450, height=300),
    )
    display(chart)

CPU times: user 140 ms, sys: 4.14 ms, total: 144 ms
Wall time: 144 ms

%%time
dfs_by_month = {}
for day_type, dow in zip(['weekday', 'weekend'], [[0, 1, 2, 3, 4], [5, 6]]):
    dow_str = '('+', '.join([str(d) for d in dow])+')'
    query = f"""
            WITH t1 AS (
                -- 1. get number of monthly trips per station, user type, year & month in all data
                SELECT start_station_id AS station_id,
                       started_at_year AS year,
                       started_at_month AS month,
                       REPLACE(user_type, ' Member', '') AS user_type,
                       COUNT(DISTINCT(trip_id)) AS trips
                FROM read_parquet({fpaths_proc_all})
                WHERE ISODOW(started_at)-1 IN {dow_str}
                GROUP BY all
            ),
            -- 2. LEFT JOIN with table indicating if station is a top-performer
            t2 AS (
                SELECT *
                FROM t1
                LEFT JOIN (
                    SELECT station_id,
                           is_top_perform_station
                    FROM df_stations
                ) USING (station_id)
            ),
            -- 3. fill any missing values in the is_top_perform_station column after LEFT JOIN
            -- with FALSE
            t3 AS (
                SELECT --fill missing values in is_top_perform_station with FALSE
                       * EXCLUDE(is_top_perform_station),
                       COALESCE(is_top_perform_station, NULL, False) AS is_top_perform_station
                FROM t2
            ),
            -- 4. calculate average monthly trips per station, station type, user type, year and month
            t4 AS (
                SELECT -- change the top-performing station column from a boolean to a string
                       (
                           CASE WHEN is_top_perform_station = True
                           THEN 'top-performer'
                           ELSE 'others' END
                       ) AS station_type,
                       user_type,
                       year,
                       month,
                       -- get number of stations
                       COUNT(DISTINCT(station_id)) AS num_stations_used,
                       strftime(make_date(year, month, 1), '%Y-%m') AS date_ym,
                       -- get trips (ridership)
                       SUM(trips) AS trips,
                       -- get average ridership
                       SUM(trips)/num_stations_used AS avg_trips_per_station,
                       strftime(make_date(2021, 10, 1), '%Y-%m') AS corporate_start
                FROM t3
                GROUP BY ALL
                ORDER BY ALL
            )
            SELECT *
            FROM t4
            """
    dfs_by_month[day_type] = run_sql_query(query).convert_dtypes()
pu.show_df(dfs_by_month['weekday'])

CPU times: user 16.3 s, sys: 691 ms, total: 17 s
Wall time: 1.75 s

-- LEFT JOIN with table indicating if station is a top-performer and fill missing values
t3 AS (
    SELECT * EXCLUDE(is_top_perform_station),
           COALESCE(is_top_perform_station, NULL, False) AS is_top_perform_station
    FROM t1
    LEFT JOIN (
        SELECT station_id,
               is_top_perform_station
        FROM df_stations
    ) USING (station_id)
),

%%time
for day_type, fname_no_ext in zip(
    ['weekday', 'weekend'],
    ['07_monthly_weekday_ridership', '08_monthly_weekend_ridership'],
):
    chart = vzu.plot_grouped_line_charts(
        dfs_by_month[day_type],
        pd.DataFrame({'date_ym': [pd.to_datetime('2022-10-01')]}),
        xvar="yearmonth(date_ym):T",
        yvar="avg_trips_per_station:Q",
        color_by_col="user_type:N",
        color_labels=['Annual', 'Casual'],
        color_values=['darkgreen', '#a1d99b'],
        legend=alt.Legend(
            titleFontSize=axis_label_fontsize,
            labelFontSize=axis_label_fontsize,
            labelColor='black',
            orient='bottom',
            direction='horizontal',
            titleOrient='left',
        ),
        annotation_text='Corporate Plan Starts' if day_type == 'weekday' else '',
        annotation_text_color='red',  # 'grey'
        annotation_text_opacity=0.5,
        annotation_text_x_loc=-175,
        annotation_text_y_loc=-95,
        xvar_rule="yearmonth(corporate_start):T",
        color_rule='red',  # '#cccccc'
        title_text=(
            'Inversion in Casual & Annual Patterns since Oct. 2021'
            if day_type == 'weekday'
            else 'Weekend Casual Ridership is Dominant since Oct. 2021'
        ),
        sub_title_text_color='#7f7f7f',
        sub_title_vertical_padding=5,
        axis_label_fontsize=14,
        x_axis_ticks=None,
        concat='column',
        fig_size=dict(width=450, height=300),
    )
    chart.save(os.path.join(figures_dir, f'{fname_no_ext}.png'))
    display(chart)

CPU times: user 577 ms, sys: 7.88 ms, total: 585 ms
Wall time: 496 ms

%%time
query = f"""
        WITH t1 AS (
            -- 1. get number of hourly trips per station and user type in 2018
            SELECT start_station_id AS station_id,
                   started_at_year AS year,
                   started_at_hour AS hour,
                   REPLACE(user_type, ' Member', '') AS user_type,
                   COUNT(DISTINCT(trip_id)) AS trips
            FROM read_parquet({fpaths_proc[2018]})
            GROUP BY all
        ),
        -- 2. LEFT JOIN with table indicating if station is a top-performer
        t2 AS (
            SELECT *
            FROM t1
            LEFT JOIN (
                SELECT station_id,
                       is_top_perform_station
                FROM df_stations
            ) USING (station_id)
        ),
        -- 3. fill any missing values in the is_top_perform_station column after LEFT JOIN
        -- with FALSE
        t3 AS (
            SELECT --fill missing values in is_top_perform_station with FALSE
                   * EXCLUDE(is_top_perform_station),
                   COALESCE(is_top_perform_station, NULL, False) AS is_top_perform_station
            FROM t2
        ),
        -- 4. calculate average hourly trips per station, station type and user type
        t4 AS (
            SELECT -- change the top-performing station column from a boolean to a string
                   (
                       CASE WHEN is_top_perform_station = True
                       THEN 'top-performer'
                       ELSE 'others' END
                   ) AS station_type,
                   user_type,
                   year,
                   hour,
                   -- get number of stations
                   COUNT(DISTINCT(station_id)) AS num_stations_used,
                   strftime(make_timestamp(year, 1, 1, hour, 0, 0), '%Y %H') AS date_yh,
                   -- get trips (ridership)
                   SUM(trips) AS trips,
                   -- get average ridership
                   SUM(trips)/num_stations_used AS avg_trips_per_station,
                   '2018 08' AS peak_ridership
            FROM t3
            GROUP BY ALL
            ORDER BY ALL
        )
        SELECT *
        FROM t4
        """
df_by_hour_2018 = run_sql_query(query).convert_dtypes()
pu.show_df(df_by_hour_2018)

CPU times: user 1.24 s, sys: 23 ms, total: 1.27 s
Wall time: 276 ms

%%time
chart = vzu.plot_grouped_line_charts(
    df_by_hour_2018,
    pd.DataFrame({'date_yh': ['2018 08']}),
    xvar="date_yh:N",
    yvar="avg_trips_per_station:Q",
    color_by_col="user_type:N",
    color_labels=['Annual', 'Casual'],
    color_values=['darkgreen', '#a1d99b'],
    legend=alt.Legend(
        titleFontSize=axis_label_fontsize,
        labelFontSize=axis_label_fontsize,
        labelColor='black',
        orient='bottom',
        direction='horizontal',
        titleOrient='left',
    ),
    annotation_text='Annual Peaks at 8AM, 5PM',
    annotation_text_color='red',  # 'grey'
    annotation_text_opacity=0.5,
    annotation_text_x_loc=60,
    annotation_text_y_loc=-85,
    xvar_rule="",
    color_rule='red',  # '#cccccc'
    title_text='Annual 2018 Trips Show Commuting-Driven Usage',
    sub_title_text_color='#7f7f7f',
    sub_title_vertical_padding=5,
    axis_label_fontsize=14,
    x_axis_ticks=df_by_hour_2018.iloc[::2, :]['date_yh'].tolist(),
    concat='column',
    fig_size=dict(width=450, height=300),
)
chart

CPU times: user 32.6 ms, sys: 70 µs, total: 32.7 ms
Wall time: 32.2 ms

%%time
dfs_by_hour = {}
for day_type, dow in zip(['weekday', 'weekend'], [[0, 1, 2, 3, 4], [5, 6]]):
    dow_str = '('+', '.join([str(d) for d in dow])+')'
    query = f"""
            WITH t1 AS (
                -- 1. get number of hourly trips per station, user type and year in all data
                SELECT start_station_id AS station_id,
                       started_at_year AS year,
                       started_at_hour AS hour,
                       REPLACE(user_type, ' Member', '') AS user_type,
                       COUNT(DISTINCT(trip_id)) AS trips
                FROM read_parquet({fpaths_proc_all})
                WHERE ISODOW(started_at)-1 IN {dow_str}
                GROUP BY all
            ),
            -- 2. LEFT JOIN with table indicating if station is a top-performer
            t2 AS (
                SELECT *
                FROM t1
                LEFT JOIN (
                    SELECT station_id,
                           is_top_perform_station
                    FROM df_stations
                ) USING (station_id)
            ),
            -- 3. fill any missing values in the is_top_perform_station column after LEFT JOIN
            -- with FALSE
            t3 AS (
                SELECT --fill missing values in is_top_perform_station with FALSE
                       * EXCLUDE(is_top_perform_station),
                       COALESCE(is_top_perform_station, NULL, False) AS is_top_perform_station
                FROM t2
            ),
            -- 4. calculate average monthly trips per station, station type, user type and year
            t4 AS (
                SELECT -- change the top-performing station column from a boolean to a string
                       (
                           CASE WHEN is_top_perform_station = True
                           THEN 'top-performer'
                           ELSE 'others' END
                       ) AS station_type,
                       user_type,
                       year,
                       hour,
                       -- get number of stations
                       COUNT(DISTINCT(station_id)) AS num_stations_used,
                       strftime(make_timestamp(year, 1, 1, hour, 0, 0), '%Y %H') AS date_yh,
                       -- get trips (ridership)
                       SUM(trips) AS trips,
                       -- get average ridership
                       SUM(trips)/num_stations_used AS avg_trips_per_station,
                       '2021 00' AS corporate_start
                FROM t3
                GROUP BY ALL
                ORDER BY ALL
            )
            SELECT *
            FROM t4
            """
    dfs_by_hour[day_type] = run_sql_query(query).convert_dtypes()
pu.show_df(dfs_by_hour['weekday'])

CPU times: user 19.2 s, sys: 607 ms, total: 19.8 s
Wall time: 2.09 s

%%time
for day_type, fname_no_ext in zip(
    ['weekday', 'weekend'],
    ['09_hourly_weekday_ridership', '10_hourly_weekend_ridership'],
):
    chart = vzu.plot_grouped_line_charts(
        dfs_by_hour[day_type],
        pd.DataFrame({'date_yh': ['2021 00']}),
        xvar="date_yh:N",
        yvar="avg_trips_per_station:Q",
        color_by_col="user_type:N",
        color_labels=['Annual', 'Casual'],
        color_values=['darkgreen', '#a1d99b'],
        legend=alt.Legend(
            titleFontSize=axis_label_fontsize,
            labelFontSize=axis_label_fontsize,
            labelColor='black',
            orient='bottom',
            direction='horizontal',
            titleOrient='left',
        ),
        annotation_text='Corporate Plan Starts' if day_type == 'weekday' else '',
        annotation_text_color='red',  # 'grey'
        annotation_text_opacity=0.5,
        annotation_text_x_loc=-80,
        annotation_text_y_loc=-115,
        xvar_rule="corporate_start:N",
        color_rule='red',  # '#cccccc'
        title_text=(
            'Since Oct. 2021, Casual Hourly Usage Dominates Annual'
            if day_type == 'weekday'
            else 'Weekend Casual Hourly Usage is 2X Annual since Oct. 2021'
        ),
        sub_title_text_color='#7f7f7f',
        sub_title_vertical_padding=5,
        axis_label_fontsize=14,
        x_axis_ticks=dfs_by_hour[day_type].iloc[::16, :]['date_yh'].tolist(),
        concat='column',
        fig_size=dict(width=450, height=300),
    )
    chart.save(os.path.join(figures_dir, f'{fname_no_ext}.png'))
    display(chart)

CPU times: user 765 ms, sys: 2.53 ms, total: 768 ms
Wall time: 578 ms

%%time
dfs_by_month_hour = {}
for day_type, dow in zip(['weekday', 'weekend'], [[0, 1, 2, 3, 4], [5, 6]]):
    dow_str = '('+', '.join([str(d) for d in dow])+')'
    query = f"""
            WITH t1 AS (
                -- 1. get number of hourly trips per station, user type, year & month in all data
                SELECT start_station_id AS station_id,
                       started_at_year AS year,
                       started_at_month AS month,
                       started_at_hour AS hour,
                       REPLACE(user_type, ' Member', '') AS user_type,
                       COUNT(DISTINCT(trip_id)) AS trips
                FROM read_parquet({fpaths_proc[2020]+fpaths_proc[2021]+fpaths_proc[2022]})
                WHERE ISODOW(started_at)-1 IN {dow_str}
                GROUP BY all
            ),
            -- 2. LEFT JOIN with table indicating if station is a top-performer
            t2 AS (
                SELECT *
                FROM t1
                LEFT JOIN (
                    SELECT station_id,
                           is_top_perform_station
                    FROM df_stations
                ) USING (station_id)
            ),
            -- 3. fill any missing values in the is_top_perform_station column after LEFT JOIN
            -- with FALSE
            t3 AS (
                SELECT --fill missing values in is_top_perform_station with FALSE
                       * EXCLUDE(is_top_perform_station),
                       COALESCE(is_top_perform_station, NULL, False) AS is_top_perform_station
                FROM t2
            ),
            -- 4. calculate average hourly trips per station, station type, user type, year and month
            t4 AS (
                SELECT -- change the top-performing station column from a boolean to a string
                       (
                           CASE WHEN is_top_perform_station = True
                           THEN 'top-performer'
                           ELSE 'others' END
                       ) AS station_type,
                       user_type,
                       year,
                       month,
                       hour,
                       -- get number of stations
                       COUNT(DISTINCT(station_id)) AS num_stations_used,
                       strftime(make_timestamp(year, month, 1, hour, 0, 0), '%Y-%m %H') AS date_yh,
                       -- get trips (ridership)
                       SUM(trips) AS trips,
                       -- get average ridership
                       SUM(trips)/num_stations_used AS avg_trips_per_station,
                       '2021-10 00' AS corporate_start
                FROM t3
                GROUP BY ALL
                ORDER BY ALL
            )
            SELECT *
            FROM t4
            """
    dfs_by_month_hour[day_type] = run_sql_query(query).convert_dtypes()
pu.show_df(dfs_by_month_hour['weekday'])

CPU times: user 13.9 s, sys: 420 ms, total: 14.3 s
Wall time: 1.55 s

%%time
for day_type in ['weekday', 'weekend']:
    chart = vzu.plot_grouped_line_charts(
        dfs_by_month_hour[day_type],
        pd.DataFrame({'date_yh': ['2021-10 00']}),
        xvar="date_yh:N",
        yvar="avg_trips_per_station:Q",
        color_by_col="user_type:N",
        color_labels=['Annual', 'Casual'],
        color_values=['darkgreen', '#a1d99b'],
        legend=alt.Legend(
            titleFontSize=axis_label_fontsize,
            labelFontSize=axis_label_fontsize,
            labelColor='black',
            orient='bottom',
            direction='horizontal',
            titleOrient='left',
        ),
        annotation_text='Corporate Plan Starts' if day_type == 'weekday' else '',
        annotation_text_color='red',  # 'grey'
        annotation_text_opacity=0.5,
        annotation_text_x_loc=-80,
        annotation_text_y_loc=-115,
        xvar_rule="corporate_start:N",
        color_rule='red',  # '#cccccc'
        title_text=(
            'Since Oct. 2021, Casual Hourly Usage Dominates Annual'
            if day_type == 'weekday'
            else 'Weekend Casual Hourly Usage is 2X Annual since Oct. 2021'
        ),
        sub_title_text_color='#7f7f7f',
        sub_title_vertical_padding=5,
        axis_label_fontsize=14,
        x_axis_ticks=(
            dfs_by_month_hour[day_type]
            .query("(station_type == 'others') & (user_type == 'Annual')")
            # show tick labels for month-hour combinations every three months
            .iloc[::24*3]
            ['date_yh']
            .tolist()
        ),
        concat='row',
        fig_size=dict(width=1_400, height=300),
    )
    display(chart)

CPU times: user 234 ms, sys: 4.03 ms, total: 238 ms
Wall time: 237 ms

%%time
query = f"""
        WITH t1 AS (
            -- 1. get number of weekday trips per station, user type and year in all data
            -- using ID
            SELECT start_station_id AS station_id,
                   started_at_year AS year,
                   DAYNAME(started_at) AS weekday,
                   REPLACE(user_type, ' Member', '') AS user_type,
                   COUNT(DISTINCT(trip_id)) AS trips
            FROM read_parquet({fpaths_proc_all})
            GROUP BY all
        ),
        -- 2. LEFT JOIN with table indicating if station is a top-performer
        t2 AS (
            SELECT *
            FROM t1
            LEFT JOIN (
                SELECT station_id,
                       is_top_perform_station
                FROM df_stations
            ) USING (station_id)
        ),
        -- 3. fill any missing values in the is_top_perform_station column after LEFT JOIN
        -- with FALSE
        t3 AS (
            SELECT --fill missing values in is_top_perform_station with FALSE
                   * EXCLUDE(is_top_perform_station),
                   COALESCE(is_top_perform_station, NULL, False) AS is_top_perform_station
            FROM t2
        ),
        -- 4. calculate average weekday trips per station, station type, user type and year
        t4 AS (
            SELECT -- change the top-performing station column from a boolean to a string
                   (
                       CASE WHEN is_top_perform_station = True
                       THEN 'top-performer'
                       ELSE 'others' END
                   ) AS station_type,
                   user_type,
                   year,
                   weekday,
                   COUNT(DISTINCT(station_id)) AS num_stations_used,
                   SUM(trips) AS trips,
                   SUM(trips)/num_stations_used AS avg_trips_per_station
            FROM t3
            GROUP BY ALL
            ORDER BY ALL
        )
        SELECT *
        FROM t4
        """
df_by_day_of_week = run_sql_query(query).convert_dtypes()
pu.show_df(df_by_day_of_week.head())

CPU times: user 13.5 s, sys: 805 ms, total: 14.3 s
Wall time: 1.5 s

%%time
weekday_colors = [
    'lightgreen', '#39a055', 'black', '#208943', 'darkgreen', '#abdda5', '#c0e6ba'
]
chart = vzu.plot_bar_chart_array(
    df_by_day_of_week,
    xvar = 'weekday:N',
    yvar = "sum(avg_trips_per_station):Q",
    color_by_col = "weekday:N",
    color_labels = {'Annual': list(day_name), 'Casual': list(day_name)},
    color_values = {"Annual": weekday_colors, 'Casual': weekday_colors},
    legend=alt.Legend(
        titleFontSize=axis_label_fontsize,
        labelFontSize=axis_label_fontsize,
        labelColor='black',
        orient='bottom',
        direction='horizontal',
        titleOrient='left',
    ),
    title_text={
        'Annual': (
            'Weekday Ridership by Annual Members outside Top-Performers is '
            'Contracting'
        ),
        'Casual': 'Weekday Ridership by Casual Members is Growing Across Entire Network',
    },
    column_var = "year:N",
    column_spacing = 5,
    column_label_color = 'grey',
    column_sort=list(day_name),
    column_label_position = 'bottom',
    sub_title_text_color="#7f7f7f",
    sub_title_vertical_padding=5,
    axis_label_fontsize=axis_label_fontsize,
    concat='column',
    column_label_align='center',
    fig_size=dict(width=105, height=300),
)
chart['Annual'].save(os.path.join(figures_dir, '11_daily_weekday.png'))
chart['Casual'].save(os.path.join(figures_dir, '12_daily_weekend.png'))
display(chart['Annual'])
display(chart['Casual'])

CPU times: user 295 ms, sys: 3.84 ms, total: 299 ms
Wall time: 233 ms

%%time
query = f"""
        WITH t1 AS (
            -- 1. get number of daily trips per station, user type, year and month in all data
            SELECT start_station_id AS station_id,
                   started_at_year AS year,
                   started_at_month AS month,
                   started_at_day AS day,
                   REPLACE(user_type, ' Member', '') AS user_type,
                   COUNT(DISTINCT(trip_id)) AS trips
            FROM read_parquet({fpaths_proc_all})
            GROUP BY all
        ),
        -- 2. LEFT JOIN with table indicating if station is a top-performer
        t2 AS (
            SELECT *
            FROM t1
            LEFT JOIN (
                SELECT station_id,
                       is_top_perform_station
                FROM df_stations
            ) USING (station_id)
        ),
        -- 3. fill any missing values in the is_top_perform_station column after LEFT JOIN
        -- with FALSE
        t3 AS (
            SELECT --fill missing values in is_top_perform_station with FALSE
                   * EXCLUDE(is_top_perform_station),
                   COALESCE(is_top_perform_station, NULL, False) AS is_top_perform_station
            FROM t2
        ),
        -- 4. calculate average daily trips per station, station type, user type, year and month
        t4 AS (
            SELECT -- change the top-performing station column from a boolean to a string
                   (
                       CASE WHEN is_top_perform_station = True
                       THEN 'top-performer'
                       ELSE 'others' END
                   ) AS station_type,
                   user_type,
                   year,
                   month,
                   day,
                   make_date(year, month, day) AS date,
                   COUNT(DISTINCT(station_id)) AS num_stations_used,
                   SUM(trips) AS trips,
                   SUM(trips)/num_stations_used AS avg_trips_per_station
            FROM t3
            GROUP BY ALL
            ORDER BY ALL
        ),
        -- 5. LEFT JOIN with daily weather data
        t5 AS (
            SELECT *,
                   (
                       CASE
                           WHEN year = 2018 THEN 19
                           WHEN year = 2019 THEN 17
                           WHEN year = 2020 THEN 17
                           WHEN year = 2021 THEN 15
                       ELSE 2.5
                       END
                   ) AS x_turn_point,
                   0.75 AS y_turn_point,
                   -- append indicator symbol for presence (2018, 2019) or absence (2020, 2021, 2022)
                   -- of turning point in ridership based on temperature
                   '↑' AS mark_turn_point
            FROM t4
            LEFT JOIN (
                SELECT time as date,
                       tmin,
                       tmax
                FROM read_parquet({[fpath_weather]})
            ) USING (date)
        )
        SELECT *,
               make_date(2021, 10, 1) AS corporate_start
        FROM t5
        """
df_daily = run_sql_query(query).convert_dtypes()
pu.show_df(df_daily)

CPU times: user 16.5 s, sys: 533 ms, total: 17 s
Wall time: 1.72 s

%%time
chart = vzu.plot_scatter_chart_grid(
    df_daily.query("year.isin(@years_temp_dependence)"),
    xvar = 'tmax:Q',
    yvar = 'avg_trips_per_station:Q',
    row_var = "year:O",
    color_by_col = 'user_type:N',
    col_var = "station_type:N",
    row_sort=years_temp_dependence,
    facet_label_color='grey',
    color_labels = ['Casual', 'Annual'],
    color_values = ['darkgreen', '#a1d99b'],
    legend=alt.Legend(
        title=None,
        titleFontSize=axis_label_fontsize,
        labelFontSize=axis_label_fontsize,
        labelColor='black',
        orient='bottom',
        direction='horizontal',
        titleOrient='left',
    ),
    y_scale='linear',
    ptitle=alt.TitleParams(
        (
            'Casual Ridership Shows Increased Resilience to Colder '
            'Temps. & Now Resembles Annual Ridership'
        ),
        anchor='start',
        dx=55,
        dy=-5,
        fontSize=axis_label_fontsize,
    ),
    marker_size=60,
    annotation_text_color = 'red',
    annotation_text_x_loc_var = 'x_turn_point:Q',
    annotation_text_y_loc_var = 'y_turn_point:Q',
    annotation_text_color_by_col = 'mark_turn_point:N',
    axis_label_fontsize = 14,
    fig_size=dict(width=375, height=300),
)
chart.save(
    os.path.join(
        figures_dir, '13_daily_max_temperature_vs_ridership.png'
    )
)
chart

CPU times: user 2.16 s, sys: 51.8 ms, total: 2.21 s
Wall time: 1.93 s

%%time
query = f"""
        WITH t1 AS (
            -- 1. get number of daily trips per station, user type, year and month in all data
            SELECT start_station_id AS station_id,
                   started_at_year AS year,
                   started_at_month AS month,
                   started_at_day AS day,
                   COUNT(DISTINCT(trip_id)) AS trips
            FROM read_parquet({fpaths_proc_all})
            WHERE started_at_year <= 2022
            GROUP BY all
        ),
        -- 2. LEFT JOIN with table indicating if station is a top-performer
        t2 AS (
            SELECT *
            FROM t1
            LEFT JOIN (
                SELECT station_id,
                       is_top_perform_station
                FROM df_stations
            ) USING (station_id)
        ),
        -- 3. fill any missing values in the is_top_perform_station column after LEFT JOIN
        -- with FALSE
        t3 AS (
            SELECT --fill missing values in is_top_perform_station with FALSE
                   * EXCLUDE(is_top_perform_station),
                   COALESCE(is_top_perform_station, NULL, False) AS is_top_perform_station
            FROM t2
        ),
        -- 4. calculate average daily trips per station, station type, year and month
        t4 AS (
            SELECT -- change the top-performing station column from a boolean to a string
                   (
                       CASE WHEN is_top_perform_station = True
                       THEN 'top-performer'
                       ELSE 'others' END
                   ) AS station_type,
                   year,
                   month,
                   day,
                   make_date(year, month, day) AS date,
                   COUNT(DISTINCT(station_id)) AS num_stations_used,
                   SUM(trips) AS trips,
                   SUM(trips)/num_stations_used AS avg_trips_per_station
            FROM t3
            GROUP BY ALL
            ORDER BY ALL
        ),
        -- 5. LEFT JOIN with minimum and maximum temperature in daily weather data
        t5 AS (
            SELECT *
            FROM t4
            LEFT JOIN (
                SELECT time as date,
                       MIN(tmin) AS tmin,
                       MAX(tmax) AS tmax
                FROM read_parquet({[fpath_weather]})
                GROUP BY ALL
            ) USING (date)
        )
        SELECT *,
               make_date(2021, 10, 1) AS corporate_start
        FROM t5
        ORDER BY station_type, date
        """
df_daily_overall = run_sql_query(query).convert_dtypes()
df_daily_overall = (
    df_daily_overall
    .assign(
        tmin=lambda df: df['tmin'].ffill(),
        tmax=lambda df: df['tmax'].ffill(),
    )
)
pu.show_df(df_daily_overall)

CPU times: user 10.7 s, sys: 532 ms, total: 11.2 s
Wall time: 1.15 s

%%time
chart = vzu.plot_multi_axis_line_chart_grid(
    df_daily_overall,
    xvar='yearmonthdate(date):T',
    line_colors={'top-performer': 'darkgreen', 'others': '#31a354'},
    y1var='avg_trips_per_station:Q',
    y2var_min='tmin:Q',
    y2var_max='tmax:Q',
    y_title_text='Daily Ridership per Station',
    y2_title_text='Temperature Range (C)',
    title_text='Total Daily Ridership is Maximized During Warmest Months',
    sub_title_text_color="#7f7f7f",
    sub_title_vertical_padding=5,
    shading_opacity=0.85,
    shading_color='lightgrey',
    axis_label_fontsize=14,
    fig_size=dict(width=700, height=360),
)
chart.save(
    os.path.join(
        figures_dir, '14_daily_max_temperature_and_ridership.png'
    )
)
chart

CPU times: user 453 ms, sys: 4.4 ms, total: 457 ms
Wall time: 333 ms

df_daily_overall_r2 = (
    df_daily_overall
    .groupby(['station_type', 'year'], as_index=False)
    .apply(lambda df: linregress(df['trips'], df['tmax'])[2], include_groups=False)
    .rename(columns={None: 'y'})
)
df_daily_overall_r2

%%time
chart = vzu.plot_simple_heatmap(
    df_daily_overall_r2,
    xvar = 'year:O',
    yvar = 'station_type:O',
    color_by_col = 'y:Q',
    color_scale = 'linear',
    annot_color_threshold = 0.8,
    grid_linewidth = 0.75,
    ptitle_text = 'Daily Trips are Correlated to Max. Daily Temperature',
    axis_label_fontsize=14,
    fig_size = dict(width=510, height=115),
)
chart.save(
    os.path.join(
        figures_dir,
        '15_daily_max_temperature_ridership_correlation.png'
    )
)
chart

CPU times: user 52.9 ms, sys: 4.03 ms, total: 56.9 ms
Wall time: 54.3 ms

recommended_hour_filters = {
    "weekday_prime": (
        "day_of_week IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')"
        "AND month IN ('May', 'June', 'July', 'August', 'September', 'October')"
        "AND hour IN (7, 8, 9, 15, 16, 17, 18)"
        "AND user_type IN ('Annual', 'Casual')"
    ),
    "weekend_prime": (
        "day_of_week IN ('Saturday', 'Sunday')"
        "AND month IN ('May', 'June', 'July', 'August', 'September', 'October')"
        "AND hour IN (13, 14, 15,16, 17, 18)"
        "AND user_type IN ('Casual')"
    ),
    "weekday_offseason": (
        "day_of_week IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday')"
        "AND month IN ('November', 'December')"
        "AND hour IN (15, 16, 17)"
        "AND user_type IN ('Annual', 'Casual')"
    ),
}
df_temporal_recommends = (
    pd.DataFrame.from_dict(recommended_hour_filters, orient='index')
    .transpose()
    .convert_dtypes()
)
with pd.option_context('display.max_colwidth', None):
    pu.show_df(df_temporal_recommends)

recommended_performance_filters = {
    "overall_weekday_weekend": (
        "is_top_perform_station_weekday = TRUE "
        "AND is_top_perform_station_weekend = TRUE "
        "AND is_top_perform_station = TRUE"
    )
}
df_performance_recommends = (
    pd.DataFrame.from_dict(recommended_performance_filters, orient='index')
    .transpose()
    .convert_dtypes()
)
with pd.option_context('display.max_colwidth', None):
    pu.show_df(df_performance_recommends)

%%time
fname_prefix = "recommendations_temporal"
_ = df_temporal_recommends.pipe(
    flut.load,
    processed_data_dir,
    fname_prefix,
    my_timezone,
    True,
)

Exported 1 rows of recommendations_temporal data to /home/jovyan/data/processed/recommendations_temporal__20240321_114636.parquet.gzip
CPU times: user 13.2 ms, sys: 68 µs, total: 13.3 ms
Wall time: 13 ms

%%time
fname_prefix = "recommendations_performance"
_ = df_performance_recommends.pipe(
    flut.load,
    processed_data_dir,
    fname_prefix,
    my_timezone,
    True,
)

Exported 1 rows of recommendations_performance data to /home/jovyan/data/processed/recommendations_performance__20240321_114636.parquet.gzip
CPU times: user 1.33 ms, sys: 68 µs, total: 1.39 ms
Wall time: 1.16 ms

packages = [
    'scipy',
    'pandas',
    'pyarrow',
    'duckdb',
    'altair',
    'vl-convert',
]
print(
    watermark(
        updated=True,
        current_date=True,
        current_time=True,
        timezone=True,
        custom_time="%Y-%m-%d %H:%M:%S %Z",
        python=True,
        machine=True,
        packages=','.join(packages),
    )
)

Last updated: 2024-03-21 15:46:36 UTC

Python implementation: CPython
Python version       : 3.11.8
IPython version      : 8.22.2

scipy     : 1.12.0
pandas    : 2.2.1
pyarrow   : 15.0.1
duckdb    : 0.10.0
altair    : 5.2.0
vl-convert: not installed

Compiler    : GCC 12.3.0
OS          : Linux
Release     : 6.6.10-76060610-generic
Machine     : x86_64
Processor   : x86_64
CPU cores   : 12
Architecture: 64bit

column	time	tavg	tmin	tmax	prcp	snow	wdir	wspd	wpgt	pres	tsun	station_id
dtype	datetime64[us]	Float64	Float64	Float64	Float64	Int64	Int64	Float64	Int64	Float64	Int64	string[python]
nunique	1915	416	404	440	150	29	355	286	59	347	0	1
missing	0	0	0	0	7	1448	75	9	721	15	1915	0
0	2018-01-01	-15.0	-21.3	-8.7	0.0	110	<NA>	19.5	<NA>	1029.1	<NA>	71624
1	2018-01-02	-10.5	-13.1	-7.8	1.0	110	241	25.6	<NA>	1025.0	<NA>	71624
2	2018-01-03	-9.9	-13.5	-6.3	0.0	100	235	23.2	<NA>	1017.4	<NA>	71624
3	2018-01-04	-14.7	-20.5	-8.9	0.0	90	314	27.5	<NA>	1013.8	<NA>	71624
4	2018-01-05	-19.0	-23.0	-15.0	0.0	80	311	26.7	<NA>	1019.1	<NA>	71624
...	...	...	...	...	...	...	...	...	...	...	...	...
1910	2023-03-27	2.9	2.0	5.0	4.9	<NA>	55	9.4	<NA>	1018.0	<NA>	71624
1911	2023-03-28	3.5	1.0	6.0	0.1	<NA>	234	10.9	<NA>	1021.9	<NA>	71624
1912	2023-03-29	1.5	-3.9	8.0	1.6	<NA>	250	24.6	<NA>	1018.4	<NA>	71624
1913	2023-03-30	-1.3	-6.5	4.0	0.0	<NA>	251	14.0	<NA>	1026.8	<NA>	71624
1914	2023-03-31	2.4	0.0	4.4	8.3	<NA>	108	9.9	<NA>	1015.0	<NA>	71624

column	year	trips	num_stations	num_bikes	frac_neighs_with_bikeshare	frac_ctracts_with_bikeshare
dtype	Int64	Int64	Int64	Int64	Int64	Int64
nunique	3	3	3	3	0	0
missing	0	0	0	0	3	3
0	2023	5800000	820	8110	<NA>	<NA>
1	2024	7000000	930	9055	<NA>	<NA>
2	2025	8200000	1045	10000	<NA>	<NA>

column	station_id	name	physical_configuration	capacity	is_charging_station	credit	Neighbourhood	Location	census_tract_id	is_active	...	is_top_perform_station_weekday	departures_weekend_last_year	arrivals_weekend_last_year	departures_weekend_last_n_years	arrivals_weekend_last_n_years	rank_weekend_deps_last_year	rank_weekend_deps_last_n_years	rank_weekend_arrs_last_year	rank_weekend_arrs_last_n_years	is_top_perform_station_weekend
dtype	Int64	string[python]	string[python]	Int64	boolean	boolean	string[python]	string[python]	string[python]	boolean	...	boolean	Int64	Int64	Int64	Int64	Int64	Int64	Int64	Int64	boolean
nunique	627	627	6	39	2	2	83	4	214	1	...	2	570	576	601	598	570	601	576	598	2
missing	0	0	0	0	0	0	0	0	0	0	...	1	0	0	0	0	0	0	0	0	0
0	7076	York St / Queens Quay W	REGULAR	57	False	True	Harbourfront-CityPlace	Downtown	5350012.04	True	...	True	14168	16020	49454	54606	1	2	1	1	True
1	7016	Bay St / Queens Quay W (Ferry Terminal)	REGULAR	35	False	True	St Lawrence-East Bayfront-The Islands	Downtown	5350013.02	True	...	True	10548	10835	40142	42497	5	5	5	5	True
2	7033	Union Station	REGULAR	43	False	True	St Lawrence-East Bayfront-The Islands	Downtown	5350013.01	True	...	True	6026	7354	17353	20216	35	46	20	33	True
3	7175	HTO Park (Queens Quay W)	REGULAR	27	False	True	Harbourfront-CityPlace	Downtown	5350012.04	True	...	True	11206	13941	41840	47634	3	4	2	3	True
4	7203	Bathurst St/Queens Quay(Billy Bishop Airport)	REGULAR	35	False	True	Fort York-Liberty Village	West of Downtown	5350008.02	True	...	True	8970	10015	38856	41360	8	6	8	6	True
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
622	7156	Salem Ave / Bloor St W	REGULAR	15	False	True	Dovercourt Village	Others	5350096.02	True	...	False	1655	1574	6196	5856	276	232	282	246	False
623	7600	Ursula Franklin St / Huron St - SMART	SMARTMAPFRAME	20	False	False	University	Downtown	5350061.00	True	...	False	953	740	1605	1254	390	467	423	486	False
624	7414	Keele St / Annette St	REGULAR	15	False	True	Junction Area	Others	5350101.00	True	...	False	694	687	2255	2283	442	412	431	401	False
625	7622	Marie Curtis Park	REGULAR	23	False	True	Long Branch	Others	5350206.01	True	...	False	741	756	2248	2347	434	413	420	398	False
626	7694	Victoria Park Ave / Dawes Rd	REGULAR	19	False	True	O'Connor-Parkview	Others	5350190.02	True	...	False	133	130	133	130	583	594	584	595	False

column	trip_id	start_station_id	started_at	start_station_name	end_station_id	ended_at	end_station_name	bike_id	user_type	started_at_year	started_at_month	started_at_day	started_at_hour	started_at_minute	ended_at_year	ended_at_month	ended_at_day	ended_at_hour	ended_at_minute
dtype	Int64	Int32	datetime64[us]	string[python]	Int64	datetime64[us]	string[python]	Int64	string[python]	Int32	Int32	Int32	Int32	Int32	Int32	Int32	Int32	Int32	Int32
nunique	3	2	1	2	2	2	1	3	1	1	1	1	1	1	1	1	1	1	2
missing	0	0	0	0	0	0	2	0	0	0	0	0	0	0	0	0	0	0	0
0	17515458	7259	2022-08-01	Lower Spadina Ave / Lake Shore Blvd	7712	2022-08-01 00:20:00	<NA>	3328	Casual Member	2022	8	1	0	0	2022	8	1	0	20
1	17515440	7208	2022-08-01	80 Clinton St North of College	7534	2022-08-01 00:07:00	Walnut Ave / Queen St W	4662	Casual Member	2022	8	1	0	0	2022	8	1	0	7
2	17515442	7259	2022-08-01	Lower Spadina Ave / Lake Shore Blvd	7712	2022-08-01 00:20:00	<NA>	4510	Casual Member	2022	8	1	0	0	2022	8	1	0	20

column	station_type	user_type	year	month	day	date	num_stations_used	trips	avg_trips_per_station	tmin	tmax	x_turn_point	y_turn_point	mark_turn_point	corporate_start
dtype	string[python]	string[python]	Int32	Int32	Int32	datetime64[us]	Int64	Int64	Float64	Float64	Float64	Float64	Float64	string[python]	datetime64[us]
nunique	2	2	6	12	31	1916	524	3895	6974	404	440	4	1	1	1
missing	0	0	0	0	0	0	0	0	0	4	4	0	0	0	0
0	others	Casual	2018	1	31	2018-01-31	5	5	1.0	-15.1	2.1	19.0	0.75	↑	2021-10-01
1	others	Casual	2018	2	1	2018-02-01	18	21	1.166667	-10.4	4.6	19.0	0.75	↑	2021-10-01
2	others	Casual	2018	2	4	2018-02-04	4	4	1.0	-9.0	1.5	19.0	0.75	↑	2021-10-01
3	others	Casual	2018	2	6	2018-02-06	12	14	1.166667	-11.3	-4.8	19.0	0.75	↑	2021-10-01
4	others	Casual	2018	2	7	2018-02-07	7	7	1.0	-13.2	-4.8	19.0	0.75	↑	2021-10-01
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
7652	others	Annual	2020	4	16	2020-04-16	285	845	2.964912	-4.8	4.9	17.0	0.75	↑	2021-10-01
7653	others	Annual	2020	5	30	2020-05-30	351	2996	8.535613	8.0	18.7	17.0	0.75	↑	2021-10-01
7654	others	Annual	2020	7	2	2020-07-02	437	4335	9.919908	21.1	35.5	17.0	0.75	↑	2021-10-01
7655	others	Annual	2018	2	3	2018-02-03	136	480	3.529412	-10.9	-2.3	19.0	0.75	↑	2021-10-01
7656	others	Annual	2019	3	23	2019-03-23	239	1107	4.631799	-6.9	5.1	17.0	0.75	↑	2021-10-01

Temporal Exploratory Data Analysis¶

About¶

Objective¶

Data¶

Notes¶

Assumptions¶

Outputs¶

User Inputs¶

Get Data¶

Daily Weather Data¶

Downtown Neighbourhoods¶

Public Holidays¶

Top-Performing Stations¶

Show Overall Network Performance¶

Explore Attributes of Stations by Station Type¶

Get the Fraction of Stations Located in Downtown Toronto¶

Get the Fraction of Stations Located In on Near Downtown Neighbourhoods¶

Get the Fraction of Stations Accepting Payment via Credit Card¶

Get the Fraction of Stations Based on Their Configuration¶

Get the Fraction of Overall and Weekday/Weekend Top-Performers¶

User Behaviour Insights¶

Show Monthly Ridership By Station Type¶

User Behaviour Insights - Show Hourly Ridership By Station Type¶

Show Ridership By Day of the Week and Station Type¶

Show Relationship Between Temperature and Ridership By Station Type¶

Recommendations¶

Discussion¶

Conclusions¶

Summary of Assumptions¶

Version Information¶

	station_type	value	variable
0	Others	25.897921	Downtown
1	Others	5.860113	East of Downtown
2	Others	14.555766	West of Downtown
3	Others	53.6862	Other Neighbourhoods
4	Top Performer	77.55102	Downtown
5	Top Performer	1.020408	East of Downtown
6	Top Performer	16.326531	West of Downtown
7	Top Performer	5.102041	Other Neighbourhoods

	station_type	year	y
0	others	2018	0.848727
1	others	2019	0.877885
2	others	2020	0.786010
3	others	2021	0.846501
4	others	2022	0.898041
5	top-performer	2018	0.874282
6	top-performer	2019	0.889999
7	top-performer	2020	0.793471
8	top-performer	2021	0.845074
9	top-performer	2022	0.896529

column	date	holiday_name	is_holiday	weekday
dtype	datetime64[us]	string[python]	boolean	string[python]
nunique	48	13	1	5
missing	0	0	0	0
0	2018-01-01	New Year's Day	True	Monday
1	2018-03-30	Good Friday	True	Friday
2	2018-07-01	Canada Day	True	Monday
3	2018-09-03	Labor Day	True	Monday
4	2018-12-25	Christmas Day	True	Tuesday
5	2018-02-19	Family Day	True	Monday
6	2018-05-21	Victoria Day	True	Monday
7	2018-10-08	Thanksgiving Day	True	Monday
8	2018-12-26	Boxing Day	True	Wednesday
9	2019-01-01	New Year's Day	True	Tuesday
10	2019-04-19	Good Friday	True	Friday
11	2019-07-01	Canada Day	True	Monday
12	2019-09-02	Labor Day	True	Monday
13	2019-12-25	Christmas Day	True	Wednesday
14	2019-02-18	Family Day	True	Monday
15	2019-05-20	Victoria Day	True	Monday
16	2019-10-14	Thanksgiving Day	True	Monday
17	2019-12-26	Boxing Day	True	Thursday
18	2020-01-01	New Year's Day	True	Wednesday
19	2020-04-10	Good Friday	True	Friday
20	2020-07-01	Canada Day	True	Wednesday
21	2020-09-07	Labor Day	True	Monday
22	2020-12-25	Christmas Day	True	Friday
23	2020-02-17	Family Day	True	Monday
24	2020-05-18	Victoria Day	True	Monday
25	2020-10-12	Thanksgiving Day	True	Monday
26	2020-12-26	Boxing Day	True	Monday
27	2021-01-01	New Year's Day	True	Friday
28	2021-04-02	Good Friday	True	Friday
29	2021-07-01	Canada Day	True	Thursday
30	2021-09-06	Labor Day	True	Monday
31	2021-12-25	Christmas Day	True	Monday
32	2021-12-27	Christmas Day (observed)	True	Monday
33	2021-02-15	Family Day	True	Monday
34	2021-05-24	Victoria Day	True	Monday
35	2021-10-11	Thanksgiving Day	True	Monday
36	2021-12-26	Boxing Day	True	Monday

column	station_type	user_type	year	month	num_stations_used	date_ym	departures	arrivals	avg_departures_per_station	avg_arrivals_per_station	peak_ridership
dtype	string[python]	string[python]	Int32	Int32	Int64	datetime64[us]	Int64	Int64	Float64	Float64	string[python]
nunique	2	2	1	12	20	12	48	48	48	48	1
missing	0	0	0	0	0	0	0	0	0	0	0
0	others	Annual	2018	1	192	2018-01-01	22522	22221	117.302083	115.734375	2018-07
1	others	Annual	2018	2	193	2018-02-01	25536	25219	132.310881	130.668394	2018-07
2	others	Annual	2018	3	192	2018-03-01	42383	41675	220.744792	217.057292	2018-07
3	others	Annual	2018	4	192	2018-04-01	44578	44226	232.177083	230.34375	2018-07
4	others	Annual	2018	5	193	2018-05-01	88022	86699	456.072539	449.217617	2018-07
5	others	Annual	2018	6	210	2018-06-01	101149	100255	481.661905	477.404762	2018-07
6	others	Annual	2018	7	245	2018-07-01	114157	113672	465.946939	463.967347	2018-07
7	others	Annual	2018	8	265	2018-08-01	112639	112759	425.05283	425.50566	2018-07
8	others	Annual	2018	9	270	2018-09-01	114040	114485	422.37037	424.018519	2018-07
9	others	Annual	2018	10	270	2018-10-01	89755	89136	332.425926	330.133333	2018-07
10	others	Annual	2018	11	270	2018-11-01	56093	55611	207.751852	205.966667	2018-07
11	others	Annual	2018	12	270	2018-12-01	45113	44430	167.085185	164.555556	2018-07
12	others	Casual	2018	1	158	2018-01-01	582	568	3.683544	3.594937	2018-07
13	others	Casual	2018	2	172	2018-02-01	914	939	5.313953	5.459302	2018-07
14	others	Casual	2018	3	188	2018-03-01	2593	2539	13.792553	13.505319	2018-07
15	others	Casual	2018	4	191	2018-04-01	4650	4561	24.34555	23.879581	2018-07
16	others	Casual	2018	5	192	2018-05-01	18371	18236	95.682292	94.979167	2018-07
17	others	Casual	2018	6	210	2018-06-01	24843	24442	118.3	116.390476	2018-07
18	others	Casual	2018	7	245	2018-07-01	28759	28411	117.383673	115.963265	2018-07
19	others	Casual	2018	8	264	2018-08-01	30440	29888	115.30303	113.212121	2018-07
20	others	Casual	2018	9	269	2018-09-01	20422	20400	75.918216	75.836431	2018-07
21	others	Casual	2018	10	267	2018-10-01	6624	6623	24.808989	24.805243	2018-07
22	others	Casual	2018	11	251	2018-11-01	1630	1693	6.494024	6.74502	2018-07
23	others	Casual	2018	12	237	2018-12-01	1173	1172	4.949367	4.945148	2018-07
24	top-performer	Annual	2018	1	77	2018-01-01	19651	19949	255.207792	259.077922	2018-07
25	top-performer	Annual	2018	2	77	2018-02-01	21394	21710	277.844156	281.948052	2018-07
26	top-performer	Annual	2018	3	77	2018-03-01	35572	36280	461.974026	471.168831	2018-07
27	top-performer	Annual	2018	4	77	2018-04-01	36763	37111	477.441558	481.961039	2018-07
28	top-performer	Annual	2018	5	77	2018-05-01	70712	72026	918.337662	935.402597	2018-07
29	top-performer	Annual	2018	6	84	2018-06-01	82588	83481	983.190476	993.821429	2018-07
30	top-performer	Annual	2018	7	85	2018-07-01	97056	97546	1141.835294	1147.6	2018-07
31	top-performer	Annual	2018	8	85	2018-08-01	93024	92899	1094.4	1092.929412	2018-07
32	top-performer	Annual	2018	9	85	2018-09-01	89627	89196	1054.435294	1049.364706	2018-07
33	top-performer	Annual	2018	10	87	2018-10-01	68707	69320	789.735632	796.781609	2018-07
34	top-performer	Annual	2018	11	88	2018-11-01	43753	44236	497.193182	502.681818	2018-07
35	top-performer	Annual	2018	12	88	2018-12-01	34849	35539	396.011364	403.852273	2018-07
36	top-performer	Casual	2018	1	77	2018-01-01	588	602	7.636364	7.818182	2018-07

Temporal Exploratory Data Analysis¶

About¶

Objective¶

Data¶

Notes¶

Assumptions¶

Outputs¶

User Inputs¶

Get Data¶

Daily Weather Data¶

Downtown Neighbourhoods¶

Bike Share Station Info (MetaData)¶

Bike Share Network Expansion Plans¶

Public Holidays¶

Top-Performing Stations¶

Processed Bike Share Ridership Data¶

Show Overall Network Performance¶

Explore Attributes of Stations by Station Type¶

Get the Fraction of Stations Located in Downtown Toronto¶

Get the Fraction of Stations Located In on Near Downtown Neighbourhoods¶

Get the Fraction of Stations Accepting Payment via Credit Card¶

Get the Fraction of Stations Based on Their Configuration¶

Get the Fraction of Overall and Weekday/Weekend Top-Performers¶

User Behaviour Insights¶

Show Monthly Ridership By Station Type¶

User Behaviour Insights - Show Hourly Ridership By Station Type¶

Show Ridership By Day of the Week and Station Type¶

Show Relationship Between Temperature and Ridership By Station Type¶

Recommendations¶

Discussion¶

Conclusions¶

Summary of Assumptions¶

Version Information¶